﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
	public class MsStd
	{
        public MsStd()
		{
		}

		public List<MstStdInfo> GetList(string value, Int16 GroupId)
		{
            List<MstStdInfo> list = new List<MstStdInfo>();
			Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_STANDARD WHERE (MS_STD_NAME LIKE '%' + @NAME + '%') AND (MS_STD_GRP_ID = @MS_STD_GRP_ID) ORDER BY MS_STD_ID");
            db.AddInParameter(cmd, "@NAME", DbType.String, value);
            db.AddInParameter(cmd, "@MS_STD_GRP_ID", DbType.Int16, GroupId);
			using (IDataReader dr = db.ExecuteReader(cmd))
			{
				while (dr.Read())
				{
                    list.Add(new MstStdInfo(dr.GetString(0), dr.GetInt16(1),dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
				}
                dr.Close();
			}

			return list;
		}

        public List<MstStdInfo> GetListActive(Int16 GroupId)
        {
            List<MstStdInfo> list = new List<MstStdInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_STANDARD WHERE (IS_ACTIVE='1') AND (MS_STD_GRP_ID = @MS_STD_GRP_ID) ORDER BY MS_STD_ID");
            db.AddInParameter(cmd, "@MS_STD_GRP_ID", DbType.Int16, GroupId);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MstStdInfo(dr.GetString(0), dr.GetInt16(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<MstStdInfo> GetListApproval(Int16 GroupId, string UpdateBy)
        {
            List<MstStdInfo> list = new List<MstStdInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_STANDARD_TMP WHERE (MS_STD_GRP_ID = @GROUP_ID) AND ((RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '')) ORDER BY MS_STD_ID");
            db.AddInParameter(cmd, "@GROUP_ID", DbType.Int16, GroupId); 
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MstStdInfo(dr.GetString(0), dr.GetInt16(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8), dr.GetString(9), dr.GetString(10)));
                }
                dr.Close();
            }
            return list;
        }

        public MstStdInfo Get(string id, Int16 GroupId)
		{
			MstStdInfo bi = new MstStdInfo();
			Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_STANDARD WHERE MS_STD_ID LIKE '' + @STD_ID + '' AND MS_STD_GRP_ID = @GROUP_ID");
            db.AddInParameter(cmd, "@STD_ID", DbType.String, id);
            db.AddInParameter(cmd, "@GROUP_ID", DbType.Int16, GroupId);
			using (IDataReader dr = db.ExecuteReader(cmd))
			{
				while (dr.Read())
				{
                    bi = new MstStdInfo(dr.GetString(0), dr.GetInt16(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8));
				}
                dr.Close();
			}

			return bi;
		}

        public MstStdInfo GetApprovalByID(string id, Int16 GroupId)
        {
            MstStdInfo bi = new MstStdInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_STANDARD_TMP WHERE MS_STD_ID LIKE '' + @STD_ID + '' AND MS_STD_GRP_ID = @GROUP_ID ");
            db.AddInParameter(cmd, "@STD_ID", DbType.String, id);
            db.AddInParameter(cmd, "@GROUP_ID", DbType.Int16, GroupId);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new MstStdInfo(dr.GetString(0), dr.GetInt16(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8), dr.GetString(9), dr.GetString(10));
                }
                dr.Close();
            }

            return bi;
        }

        public int Save(MstStdInfo bi)
		{
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                            	UPDATE TBL_MS_STANDARD_TMP SET
            		            MS_STD_NAME = LEFT(@MS_STD_NAME,50),
                                IS_ACTIVE = @IS_ACTIVE,
                                UPDATE_BY = @UPDATE_BY, 
                                UPDATE_DATE = @UPDATE_DATE, 
                                ACT_TYPE = @ACT_TYPE,
                                MS_STD_ID_ORIG = @MS_STD_ID_ORIG,
                                MS_STD_TYPE = LEFT(@MS_STD_TYPE,10)
            	            WHERE MS_STD_ID LIKE '' + @MS_STD_ID + '' AND MS_STD_GRP_ID = @MS_STD_GRP_ID ;
            	            IF @@ROWCOUNT = 0
                            INSERT INTO TBL_MS_STANDARD_TMP(MS_STD_ID, MS_STD_GRP_ID, MS_STD_NAME, IS_ACTIVE, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE, MS_STD_ID_ORIG, MS_STD_TYPE)
                            VALUES(@MS_STD_ID, @MS_STD_GRP_ID, LEFT(@MS_STD_NAME,50), @IS_ACTIVE, @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE, @MS_STD_ID_ORIG, LEFT(@MS_STD_TYPE,10))
                            ");
                db.AddInParameter(cmd, "@MS_STD_ID", DbType.String, bi.MstID.ToUpper());
                db.AddInParameter(cmd, "@MS_STD_GRP_ID", DbType.Int16, bi.MstGrpID);
                db.AddInParameter(cmd, "@MS_STD_NAME", DbType.String, bi.MstName);
                db.AddInParameter(cmd, "@IS_ACTIVE", DbType.String, bi.IsActive);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, bi.EntryBy);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, bi.EntryDate);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, bi.UpdateBy);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, bi.UpdateDate);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, bi.ActType);
                db.AddInParameter(cmd, "@MS_STD_ID_ORIG", DbType.String, bi.OldID);
                db.AddInParameter(cmd, "@MS_STD_TYPE", DbType.String, bi.MstType);

                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
		}

        public void Approve(string id, Int16 GroupId, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("MSTR_STD_APPROVAL"); 

            db.AddInParameter(cmd, "@MS_STD_ID", DbType.String, id);
            db.AddInParameter(cmd, "@MS_STD_GRP_ID", DbType.Int16, GroupId);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

            db.ExecuteNonQuery(cmd);
        }

        public void Reject(string id, Int16 GroupId)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = "DELETE FROM TBL_MS_STANDARD_TMP WHERE MS_STD_ID LIKE '" + id + "' AND MS_STD_GRP_ID = " + GroupId;
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.ExecuteNonQuery(cmd);
        }

        public List<MstStdInfo> GetListDropdown(Int16 GroupId)
        {
            List<MstStdInfo> list = new List<MstStdInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = "SELECT * FROM TBL_MS_STANDARD WHERE IS_ACTIVE = '1' AND MS_STD_GRP_ID = " + GroupId + " ORDER BY MS_STD_NAME";
            DbCommand cmd = db.GetSqlStringCommand(sql);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MstStdInfo(dr.GetString(0), dr.GetInt16(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<MstStdInfo> GetTypeDebiturNewAndSahabatLama()
        {
            List<MstStdInfo> list = new List<MstStdInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_STANDARD WHERE  MS_STD_GRP_ID = 9 AND RTRIM(LTRIM(UPPER(MS_STD_ID))) = 'NEW' ORDER BY MS_STD_NAME");

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MstStdInfo(dr.GetString(0), dr.GetInt16(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<MstStdInfo> GetTypeDebiturExisting()
        {
            List<MstStdInfo> list = new List<MstStdInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_STANDARD WHERE  MS_STD_GRP_ID = 9 AND RTRIM(LTRIM(UPPER(MS_STD_ID))) IN ('NEW','TUID','TUIB') ORDER BY MS_STD_NAME");

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new MstStdInfo(dr.GetString(0), dr.GetInt16(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
                }
                dr.Close();
            }
            return list;
        }
	}
}
